/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.TourDetailDTO;
import east2west.dto.TourServiceDTO;
import east2west.interfaces.TourDetailInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author thanhluan
 */
public class TourDetailDAO implements TourDetailInterface {

    Connection con = null;
    CallableStatement call = null;
    ResultSet rs = null;
    float sumPrice = 0;

    public ArrayList<TourDetailDTO> readByAll() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_View_TourDetail}");
            rs = call.executeQuery();
            ArrayList<TourDetailDTO> list = new ArrayList<TourDetailDTO>();
            while (rs.next()) {
                TourDetailDTO dto = new TourDetailDTO();
                dto.setTourDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setTourServiceId(rs.getInt(3));
                dto.setDetail(rs.getString(4));
                dto.setImagesUrl(rs.getString(5));
                dto.setPrice(rs.getFloat(6));
                dto.setIsDelete(rs.getByte(7));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourDetailDTO> findByPrice(float price) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_View_TourDetail_FindPrice(?)}");
            call.setFloat(1, price);
            ArrayList<TourDetailDTO> list = new ArrayList<TourDetailDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                TourDetailDTO dto = new TourDetailDTO();
                dto.setTourDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setTourServiceId(rs.getInt(3));
                dto.setDetail(rs.getString(4));
                dto.setImagesUrl(rs.getString(5));
                dto.setPrice(rs.getFloat(6));
                dto.setIsDelete(rs.getByte(7));
                list.add(dto);
            }
            return list;
        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<TourDetailDTO> findByTourDetailDto(TourDetailDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public TourDetailDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_View_TourDetail_FindId(?)}");
            call.setInt(1, id);
            TourDetailDTO dto = null;
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new TourDetailDTO();
                dto.setTourDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setTourServiceId(rs.getInt(3));
                dto.setDetail(rs.getString(4));
                dto.setImagesUrl(rs.getString(5));
                dto.setPrice(rs.getFloat(6));
                dto.setIsDelete(rs.getByte(7));
            }
            return dto;
        } catch (SQLException ex) {
            return null;
        }

    }

    public boolean create(TourDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Insert_TourDetails(?,?,?,?,?)}");
            call.setInt(1, dto.getTourId());
            call.setInt(2, dto.getTourServiceId());
            call.setString(3, dto.getDetail());
            call.setString(4, dto.getImagesUrl());
            call.setFloat(5, dto.getPrice());
            call.executeUpdate();
            return true;
        } catch (SQLException ex) {

            return false;

        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(TourDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_TourDetails(?,?,?,?)}");
            call.setInt(1, dto.getTourDetailId());
            call.setInt(2, dto.getTourServiceId());
            call.setString(3, dto.getDetail());
            call.setFloat(4, dto.getPrice());
            call.executeUpdate();
            return true;
        } catch (SQLException ex) {

            return false;

        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(TourDetailDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public float getSumPriceTourDetail(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("select sum(Price) as 'Sum' from V_TourDetail_Info where TourId = ? Group by TourId");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                sumPrice = rs.getFloat("Sum");
            }
            return sumPrice;
        } catch (SQLException ex) {
            return 0;
        }
    }

    public boolean delete(int id) {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_TourDetails(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            return true;
        } catch (SQLException ex) {

            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }


    }

    public TourDetailDTO getTopOnlyImage(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT TOP(1) * FROM V_TourDetail_Info WHERE TourId = ?");
            call.setInt(1, id);
            TourDetailDTO dto = null;
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new TourDetailDTO();
                dto.setTourDetailId(rs.getInt(1));
                dto.setTourId(rs.getInt(2));
                dto.setTourServiceId(rs.getInt(3));
                dto.setDetail(rs.getString(4));
                dto.setImagesUrl(rs.getString(5));
                dto.setPrice(rs.getFloat(6));
                dto.setIsDelete(rs.getByte(7));
            }
            return dto;
        } catch (SQLException ex) {
            return null;
        }
    }
//     public TourDetailDTO getTopOnlyImageSearch(int idT,int idL) {
//        try {
//            con = DatabaseUtil.getConnection();
//            call = con.prepareCall("SELECT  * FROM V_TourSearchImage WHERE TourId = ? and LocationId=?");
//            call.setInt(1, id);
//            TourDetailDTO dto = null;
//            rs = call.executeQuery();
//            while (rs.next()) {
//                dto = new TourDetailDTO();
//                dto.setTourDetailId(rs.getInt(1));
//                dto.setTourId(rs.getInt(2));
//                dto.setTourServiceId(rs.getInt(3));
//                dto.setDetail(rs.getString(4));
//                dto.setImagesUrl(rs.getString(5));
//                dto.setPrice(rs.getFloat(6));
//                dto.setIsDelete(rs.getByte(7));
//            }
//            return dto;
//        } catch (SQLException ex) {
//            return null;
//        }
//    }


    public ArrayList<TourServiceDTO> getSerViceName(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_TourServiceName WHERE TourId=?");
            call.setInt(1, id);
            TourServiceDTO dto = null;
            ArrayList<TourServiceDTO> listServiceName = new ArrayList<TourServiceDTO>();
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new TourServiceDTO();
                TourDetailDTO detailDTO = new TourDetailDTO();
                detailDTO.setTourDetailId(rs.getInt(1));
                dto.setTourDetailDTO(detailDTO);
                detailDTO.setTourId(rs.getInt(2));
                dto.setTourDetailDTO(detailDTO);
                dto.setTourServiceId(rs.getInt(3));
                dto.setTourServiceName(rs.getString(4));
                detailDTO.setDetail(rs.getString(5));
                dto.setTourDetailDTO(detailDTO);
                detailDTO.setImagesUrl(rs.getString(6));
                dto.setTourDetailDTO(detailDTO);
                detailDTO.setPrice(rs.getFloat(7));
                dto.setTourDetailDTO(detailDTO);

                listServiceName.add(dto);
            }
            return listServiceName;
        } catch (SQLException ex) {
            return null;
        }

    }
}
